package jp.ac.u_tokyo.dch.dao.user;

import java.util.ArrayList;
import java.util.List;

import jp.ac.u_tokyo.dch.core.db.CustomPreparedStatement;
import jp.ac.u_tokyo.dch.core.db.CustomResultSet;
import jp.ac.u_tokyo.dch.core.db.DBConnection;
import jp.ac.u_tokyo.dch.core.exceptions.DBException;
import jp.ac.u_tokyo.dch.model.db.UserGroup;
import jp.ac.u_tokyo.dch.util.common.CommonDBConnection;

/**
 * @version 1.1
**/
public class UserGroupDao {


	/**
	 * DBコネクションオブジェクト
	 */
	private DBConnection dbconn = null;

	/**
	 * 生成者
	 *
	 * @param DBコネクションオブジェクト
	 */
	public UserGroupDao(DBConnection dbconn) {
		this.dbconn = dbconn;
	}

	/**
	 * ユーザグループリストを取得する
	 * @return ユーザグループリスト
	 * @throws DBException 例外
	 */
	public List<UserGroup> selectAll() throws DBException {

		List<UserGroup> groupList = new ArrayList<UserGroup>();

		String sqlQuery = "select * from user_group";

		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try {
			ps = dbconn.prepareStatement(sqlQuery.toString());
			rs = ps.executeQuery();

			while (rs.next()) {

				UserGroup userGroup = new UserGroup();
				userGroup.setId(rs.getString("id"));
				userGroup.setName(rs.getString("name"));
				userGroup.setCreateUser(rs.getString("createuser"));
				userGroup.setCreateDate(rs.getString("createdate"));
				userGroup.setUpdateUser(rs.getString("updateuser"));
				userGroup.setUpdateDate(rs.getString("updatedate"));

				groupList.add(userGroup);
			}

		} catch (DBException dbe) {
			throw dbe;
		} finally {
			CommonDBConnection.closeResource(ps, rs);
		}
		return groupList;
	}

	/**
	 * ユーザグループを取得する
	 * @param id
	 * @return ユーザグループ
	 * @throws DBException 例外
	 */
	public UserGroup selectById(String id) throws DBException {

		UserGroup userGroup = null;

		String sqlQuery = "select * from user_group where id = ?";

		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try {
			ps = dbconn.prepareStatement(sqlQuery.toString());
			ps.setString(1, id);
			rs = ps.executeQuery();

			if (rs.next()) {

				userGroup = new UserGroup();
				userGroup.setId(rs.getString("id"));
				userGroup.setName(rs.getString("name"));
				userGroup.setCreateUser(rs.getString("createuser"));
				userGroup.setCreateDate(rs.getString("createdate"));
				userGroup.setUpdateUser(rs.getString("updateuser"));
				userGroup.setUpdateDate(rs.getString("updatedate"));
			}

		} catch (DBException dbe) {
			throw dbe;
		} finally {
			CommonDBConnection.closeResource(ps, rs);
		}
		return userGroup;
	}

	/**
	 * IDグループをチェックする
	 * @param id ユーザID
	 * @return true : 存在する、false：存在しない
	 * @throws Exception 例外
	 */
	public boolean checkId(String id)throws DBException{

		boolean isExists = false;
		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		String sql = "select * from user_group where id=?";
		try{
			ps = dbconn.prepareStatement(sql);
			ps.setString(1, id);
			rs = ps.executeQuery();
			isExists = rs.next();
		}catch(DBException e) {
			throw e;
		} finally{
			CommonDBConnection.closeResource(ps, rs);
		}
		return isExists;
	}

	/**
	 * 更新する
	 * @param userGroup ユーザグループ
	 * @return 更新件数
	 * @throws DBException 例外
	 */
	public int update(UserGroup userGroup) throws DBException {

		int reslut = 0;
		CustomPreparedStatement ps = null;

		try{
			String query = "UPDATE user_group SET name = ?, updateuser = ?, updatedate = sysdate() WHERE id = ?";
			ps = dbconn.prepareStatement(query);

			int index = 1;
			ps.setString(index++, userGroup.getName());
			ps.setString(index++, userGroup.getUpdateUser());
			ps.setString(index++, userGroup.getId());

			reslut = ps.executeUpdate();
		}catch(DBException dbe){
			throw dbe;
		}finally{
			if(ps != null) ps.close();
		}

		return reslut;
	}

	/**
	 * 削除する
	 * @param userGroup ユーザグループ
	 * @return 削除件数
	 * @throws DBException 例外
	 */
	public int delete(UserGroup userGroup) throws DBException {

		int reslut = 0;
		CustomPreparedStatement ps = null;

		try{
			String query = "DELETE FROM user_group WHERE id = ?";
			ps = dbconn.prepareStatement(query);

			int index = 1;
			ps.setString(index++, userGroup.getId());
			reslut = ps.executeUpdate();
		}catch(DBException dbe){
			throw dbe;
		}finally{
			if(ps != null) ps.close();
		}

		return reslut;
	}

	/**
	 * 挿入する
	 * @param userGroup ユーザグループ
	 * @throws DBException 例外
	 */
	public int insert(UserGroup userGroup) throws DBException {

		int reslut = 0;
		CustomPreparedStatement ps = null;

		try{
			String query = "INSERT INTO user_group (id,name,createuser,createdate,updateuser,updatedate)  VALUES (?,?,?,sysdate(),?,sysdate())";
			ps = dbconn.prepareStatement(query);

			int index = 1;
			ps.setString(index++, userGroup.getId());
			ps.setString(index++, userGroup.getName());
			ps.setString(index++, userGroup.getCreateUser());
			ps.setString(index++, userGroup.getUpdateUser());

			reslut = ps.executeUpdate();
		}catch(DBException dbe){
			throw dbe;
		}finally{
			if(ps != null) ps.close();
		}

		return reslut;
	}

}
